package com.apache.excp;

import com.apache.tools.StrUtil;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description: excel导出工具类
 * @author: Hou Dayu
 * @date: Created in 2020/8/27
 */
public class ExcelExpUtil {

    /**
     * 生成XLS格式工作薄对象
     */
    public static HSSFWorkbook getHSSFWorkbook(){
        HSSFWorkbook workbook = new HSSFWorkbook();
        return workbook;
    }

    /**
     * 生成XLSx格式工作薄对象
     */
    public static SXSSFWorkbook getSXSSFWorkbook(int rowAccessWindowSize){
        SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
        return workbook;
    }

    public static void doInvoke(Workbook workbook,String sheetName,List<Map> cells, List<Map> params,int rowNum) throws Exception {
        if(workbook instanceof HSSFWorkbook){
            doInvokeForXls((HSSFWorkbook)workbook, sheetName, cells,params, rowNum);
        } else {
            doInvokeForXlsx((SXSSFWorkbook)workbook, sheetName, cells,params, rowNum);
        }
    }
    /**
     * 生成excel文件
     * @param workbook 工作薄
     * @param params 等导出的数据
     */
   public static void doInvokeForXls(HSSFWorkbook workbook,String sheetName,List<Map> cells, List<Map> params,int rowNum) throws Exception {
       int infoSize = params.size();
       HSSFSheet sheet = null;
       try {
           sheet = workbook.createSheet(sheetName);
       } catch (Exception e){
           sheet = workbook.getSheet(sheetName);
       }
       //创建Excel工作薄对象
      /* HSSFWorkbook workbook = null;
       if(file.exists() && rowNum > 0){
           POIFSFileSystem ps=new POIFSFileSystem(new FileInputStream(file)); //使用POI提供的方法得到excel的信息
           workbook = new HSSFWorkbook(ps);
           sheet = workbook.getSheet(sheetName);
       } else {
           workbook = new HSSFWorkbook();
       }
       if(sheet == null){//创建Excel工作表对象
           sheet = workbook.createSheet(sheetName);
       }*/
       if(StrUtil.isEmpty(cells)){
           Map<String,String> one = params.get(0);
           List<String> titles = new ArrayList();
           int t=0;
           HSSFRow rowTitle = null;
           for(String title: one.keySet()){//设置表头
               if(rowNum == 0){
                   if(t==0) {
                       rowTitle = sheet.createRow(rowNum);
                   }
                   rowTitle.createCell(t).setCellValue(title);
               }
               titles.add(title);
               t++;
           }
           if (rowNum == 0) {
               rowNum = 1;
           }
           for(int i=0; i<infoSize; i++){
               Map<String, Object> map = params.get(i);
               int cellNum = 0;
               HSSFRow row = sheet.createRow(rowNum);
               for(String title: titles){//填充内容
                   String valStr = StrUtil.doNull(String.valueOf(map.get(title)),"");
                   row.createCell(cellNum).setCellValue(valStr);
                   cellNum++;
               }
               rowNum++;
           }
       } else {
           if (rowNum == 0) {
               HSSFRow rowTitle = sheet.createRow(rowNum);
               int t = 0;
               for (Map<String, Object> mt : cells) {//设置表头
                   rowTitle.createCell(t).setCellValue(String.valueOf(mt.get("title")));
                   t++;
               }
               rowNum = 1;
           }

           for (int i = 0; i < infoSize; i++) {
               Map<String, Object> map = params.get(i);
               int cellNum = 0;
               HSSFRow row = sheet.createRow(rowNum);
               for (Map<String, Object> mt : cells) {//填充内容
                   Map<String, String> conf = (Map) mt.get("attrName");
                   String tableName = conf.get("tableName");
                   String val = null;
                   if(map.containsKey(tableName)){
                       Map<String, Object> info = (Map)map.get(tableName);
                       val =  String.valueOf(info.get(conf.get("attrName")));
                   } else {
                       val = String.valueOf(map.get(conf.get("attrName")));
                   }
                   if (mt.containsKey("data_dict")) {
                       val = getValue(val, (Map) mt.get("data_dict"));
                   }
                   row.createCell(cellNum).setCellValue(StrUtil.doNull(val,""));
                   cellNum++;
               }
               rowNum++;
           }
       }
   }

    /**
     * 生成excel文件
     * @param workbook 工作薄
     * @param params 等导出的数据
     */
    public static void doInvokeForXlsx(SXSSFWorkbook workbook,String sheetName,List<Map> cells, List<Map> params,int rowNum) throws Exception {
        int infoSize = params.size();
        //创建Excel工作薄对象
        //SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        //SXSSFSheet sheet = workbook.createSheet(sheetName);
        SXSSFSheet sheet = null;
        try {
            sheet = workbook.createSheet(sheetName);
        } catch (Exception e){
            sheet = workbook.getSheet(sheetName);
        }
        if(StrUtil.isEmpty(cells)){
            Map<String,String> one = params.get(0);
            List<String> titles = new ArrayList();
            int t=0;
            SXSSFRow rowTitle = null;
            for(String title: one.keySet()){//设置表头
                if(rowNum == 0){
                    if(t==0) {
                        rowTitle = sheet.createRow(rowNum);
                    }
                    rowTitle.createCell(t).setCellValue(title);
                }
                titles.add(title);
                t++;
            }
            if (rowNum == 0) {
                rowNum = 1;
            }
            for(int i=0; i<infoSize; i++){
                Map<String, Object> map = params.get(i);
                int cellNum = 0;
                SXSSFRow row = sheet.createRow(rowNum);
                for(String title: titles){//填充内容
                    String valStr = StrUtil.doNull(String.valueOf(map.get(title)),"");
                    row.createCell(cellNum).setCellValue(valStr);
                    cellNum++;
                }
                rowNum++;
            }
        } else {
            if (rowNum == 0) {
                SXSSFRow rowTitle = sheet.createRow(rowNum);
                int t = 0;
                for (Map<String, Object> mt : cells) {//设置表头
                    rowTitle.createCell(t).setCellValue(String.valueOf(mt.get("title")));
                    t++;
                }
                rowNum = 1;
            }

            for (int i = 0; i < infoSize; i++) {
                Map<String, Object> map = params.get(i);
                int cellNum = 0;
                SXSSFRow row = sheet.createRow(rowNum);
                for (Map<String, Object> mt : cells) {//填充内容
                    Map<String, String> conf = (Map) mt.get("attrName");
                    String tableName = conf.get("tableName");
                    String val = null;
                    if(map.containsKey(tableName)){
                        Map<String, Object> info = (Map)map.get(tableName);
                        val =  String.valueOf(info.get(conf.get("attrName")));
                    } else {
                        val = String.valueOf(map.get(conf.get("attrName")));
                    }
                    if (mt.containsKey("data_dict")) {
                        val = getValue(val, (Map) mt.get("data_dict"));
                    }
                    row.createCell(cellNum).setCellValue(StrUtil.doNull(val,""));
                    cellNum++;
                }
                rowNum++;
            }
        }
    }

    public static void writeXlsx(Workbook workbook, String outFilePath, String outFileName){
        File file = new File(outFilePath +"/"+ outFileName);
        if(file.exists()){
            file.delete();
        }
        //文档输出
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(outFilePath +"/"+ outFileName);
            workbook.write(out);
            // 将此workbook对应的临时文件删除
            if(workbook instanceof SXSSFWorkbook) {
                ((SXSSFWorkbook)workbook).dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(out);
        }
    }

    public static void writeXls(HSSFWorkbook workbook,String outFilePath,String outFileName){
        File file = new File(outFilePath +"/"+ outFileName);
        if(file.exists()){
            file.delete();
        }
        //文档输出
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(outFilePath +"/"+ outFileName);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(out);
        }
    }

    /**
     * 获取数据字典对的翻译值
     */
   private static String getValue(String dataVal, Map<String,String> dicts){
       if(null !=dicts && !dicts.isEmpty()){
           if(dicts.containsKey(dataVal)){
               return dicts.get(dataVal);
           }
       }
       return dataVal;
   }

    public static void main(String[] args) throws Exception {
        String outFilePath = "E:/upload";
        List<Map> rows = new ArrayList<>();
        for(int q=0;q<10000;q++){
            Map map = new HashMap();
            map.put("cell_1","value_1_"+q);
            map.put("cell_2","value_2_"+q);
            map.put("cell_3","value_3_"+q);
            map.put("cell_4","value_4_"+q);
            rows.add(map);
        }
        List<Map> cells = new ArrayList();
        for(int q=0;q<4;q++){
            Map<String,String> map = new HashMap<>();
            map.put("attrName","cell_"+(q+1));
            map.put("expTitleType","CN");
            map.put("attrTitle","表头"+q);
            map.put("tableName", "tableName");
            Map<String,Object> dm = new HashMap();
            dm.put("attrName",map);
            String title = "CN".equals(map.get("expTitleType")) ? map.get("attrTitle"):map.get("attrName");
            dm.put("title",title);
            cells.add(dm);
        }
        SXSSFWorkbook workbook = ExcelExpUtil.getSXSSFWorkbook(100);
        ExcelExpUtil.doInvokeForXlsx(workbook,"sheet_0",cells,rows,0);
        ExcelExpUtil.doInvokeForXlsx(workbook,"sheet_0",cells,rows,10001);
        ExcelExpUtil.writeXlsx(workbook,outFilePath,"测试.xlsx");
    }
}
